﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using vnFingerPrint.BusinessObject;
using vnCommon;
namespace vnFingerPrint.DataAccess
{
    public static class Users_DataAccess
    {
        //Select Users
        public static List<Users_Object> SelectAll()
        {
            string SelectQuery = @"Select * from [Users]";
            List<Users_Object> result = new List<Users_Object>();
            DataTable tb = commonDatabase.GetSQLDataTable(clsConnectionString.GetConnectionString(), SelectQuery);
            foreach (DataRow row in tb.Rows)
            {
                Users_Object newObject = new Users_Object();
                newObject.UserName = (string)row["UserName"];
                if (row["Password"].ToString() != "") newObject.Password = (string)row["Password"];
                if (row["Email"].ToString() != "") newObject.Email = (string)row["Email"];
                if (row["MobilePhone"].ToString() != "") newObject.MobilePhone = (string)row["MobilePhone"];
                if (row["ImageUrl"].ToString() != "") newObject.ImageUrl = (string)row["ImageUrl"];
                if (row["FullName"].ToString() != "") newObject.FullName = (string)row["FullName"];
                if (row["ChucVu"].ToString() != "") newObject.ChucVu = (string)row["ChucVu"];
                if (row["DonViCongTac"].ToString() != "") newObject.DonViCongTac = (string)row["DonViCongTac"];
                if (row["Birthday"].ToString() != "") newObject.Birthday = (DateTime)row["Birthday"];
                if (row["PermissionId"].ToString() != "") newObject.PermissionId = int.Parse(row["PermissionId"].ToString());
                result.Add(newObject);
            }
            return result;
        }
        //Insert Users
        public static int Insert(Users_Object newObject)
        {
            string InsertQuery = "Insert Into [Users]";
            List<SqlParameter> paralist = new List<SqlParameter>();
            string _val = "", _para = "";
            _val += "UserName, "; _para += "@UserName, ";
            paralist.Add(new SqlParameter("UserName", newObject.UserName));
            if (newObject.Password != null)
            {
                _val += "Password, "; _para += "@Password, ";
                paralist.Add(new SqlParameter("Password", newObject.Password));
            }
            if (newObject.Email != null)
            {
                _val += "Email, "; _para += "@Email, ";
                paralist.Add(new SqlParameter("Email", newObject.Email));
            }
            if (newObject.MobilePhone != null)
            {
                _val += "MobilePhone, "; _para += "@MobilePhone, ";
                paralist.Add(new SqlParameter("MobilePhone", newObject.MobilePhone));
            }
            if (newObject.ImageUrl != null)
            {
                _val += "ImageUrl, "; _para += "@ImageUrl, ";
                paralist.Add(new SqlParameter("ImageUrl", newObject.ImageUrl));
            }
            if (newObject.FullName != null)
            {
                _val += "FullName, "; _para += "@FullName, ";
                paralist.Add(new SqlParameter("FullName", newObject.FullName));
            }
            if (newObject.ChucVu != null)
            {
                _val += "ChucVu, "; _para += "@ChucVu, ";
                paralist.Add(new SqlParameter("ChucVu", newObject.ChucVu));
            }
            if (newObject.DonViCongTac != null)
            {
                _val += "DonViCongTac, "; _para += "@DonViCongTac, ";
                paralist.Add(new SqlParameter("DonViCongTac", newObject.DonViCongTac));
            }
            if (newObject.Birthday != null)
            {
                _val += "Birthday, "; _para += "@Birthday, ";
                paralist.Add(new SqlParameter("Birthday", newObject.Birthday));
            }
            if (newObject.PermissionId != 0)
            {
                _val += "PermissionId, "; _para += "@PermissionId, ";
                paralist.Add(new SqlParameter("PermissionId", newObject.PermissionId));
            }
            InsertQuery += "(" + _val + ") Values (" + _para + ")";
            InsertQuery = InsertQuery.Replace(", )", ")");
            return commonDatabase.ExecuteNonQuerySQL(clsConnectionString.GetConnectionString(), InsertQuery, paralist);
        }

        //Get an Object of Users
        public static Users_Object GetObject(string _UserName)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();
            Users_Object newObject = new Users_Object();
            string SelectQuery = "Select * from [Users] where (1=1) and [UserName]= @UserName";
            paralist.Add(new SqlParameter("UserName", _UserName));
            DataTable tb = commonDatabase.GetSQLDataTable(clsConnectionString.GetConnectionString(), SelectQuery, paralist);
            if (tb.Rows.Count < 1) return null;
            DataRow row = tb.Rows[0];
            newObject.UserName = (string)row["UserName"];
            if (row["Password"].ToString() != "") newObject.Password = (string)row["Password"];
            if (row["Email"].ToString() != "") newObject.Email = (string)row["Email"];
            if (row["MobilePhone"].ToString() != "") newObject.MobilePhone = (string)row["MobilePhone"];
            if (row["ImageUrl"].ToString() != "") newObject.ImageUrl = (string)row["ImageUrl"];
            if (row["FullName"].ToString() != "") newObject.FullName = (string)row["FullName"];
            if (row["ChucVu"].ToString() != "") newObject.ChucVu = (string)row["ChucVu"];
            if (row["DonViCongTac"].ToString() != "") newObject.DonViCongTac = (string)row["DonViCongTac"];
            if (row["Birthday"].ToString() != "") newObject.Birthday = (DateTime)row["Birthday"];
            if (row["PermissionId"].ToString() != "") newObject.PermissionId = int.Parse(row["PermissionId"].ToString());
            return newObject;
        }
        //Save changes of an Object to database Users
        public static int SaveChanged(Users_Object UpdatingObject)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();
            string UpdateQuery =
            @"Update Users SET
               Password= @Password,
               Email= @Email,
               MobilePhone= @MobilePhone,
               ImageUrl= @ImageUrl,
               FullName= @FullName,
               ChucVu= @ChucVu,
               DonViCongTac= @DonViCongTac,
               Birthday= @Birthday,
               PermissionId=@PermissionId
            Where UserName= @UserName";
            paralist.Add(new SqlParameter("UserName", UpdatingObject.UserName));
            paralist.Add(new SqlParameter("Password", UpdatingObject.Password));
            paralist.Add(new SqlParameter("Email", UpdatingObject.Email));
            paralist.Add(new SqlParameter("MobilePhone", UpdatingObject.MobilePhone));
            paralist.Add(new SqlParameter("ImageUrl", UpdatingObject.ImageUrl));
            paralist.Add(new SqlParameter("FullName", UpdatingObject.FullName));
            paralist.Add(new SqlParameter("ChucVu", UpdatingObject.ChucVu));
            paralist.Add(new SqlParameter("DonViCongTac", UpdatingObject.DonViCongTac));
            paralist.Add(new SqlParameter("Birthday", UpdatingObject.Birthday));
            paralist.Add(new SqlParameter("PermissionId", UpdatingObject.PermissionId));
            return commonDatabase.ExecuteNonQuerySQL(clsConnectionString.GetConnectionString(), UpdateQuery, paralist);
        }
        //Delete an Object in database Users
        public static int Delete(Users_Object DeletingObject)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();
            string DeleteQuery =
            @"Delete [Users]
            Where UserName= @UserName";
            paralist.Add(new SqlParameter("UserName", DeletingObject.UserName));
            return commonDatabase.ExecuteNonQuerySQL(clsConnectionString.GetConnectionString(), DeleteQuery, paralist);
        }

        //Human code below
        
        /// <summary>
        /// Return three state: -1 means username is not exist, 0 means wrong password, 1 is pass
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static int Login(string username, string password)
        {
            Users_Object u = GetObject(username);
            if (u == null) return -1;
            if (u.Password != password) return 0;
            return 1;
        }
        public static bool IsExisting(string username)
        {
            Users_Object u = GetObject(username);
            if (u == null) return false;
            return true;
        }
    }
}